<?php

  function getPageTags($url, $url_param) {
    $return_string = "";
   
    $query = "SELECT id FROM WebPage WHERE url = '" . $url . "'";
    $result = mysql_query($query);
    $webpage_id = -1;
    if (mysql_num_rows($result) == 0) {
      $webpage_id = registerPage($url);
    } else {
      $result_row = mysql_fetch_assoc($result);
      $webpage_id = $result_row['id'];
    }

    $webpagequery_id = getPageParameterId($webpage_id, $url_param);

    $query = "SELECT TagCount.tag_id, text as tag, number, IFNULL(total_rating, 0) AS total_rating, "
           . "ROUND(((6/(tab_max.max_font+1))) * (number+IFNULL(total_rating, 0)-tab_max.min_font)) AS font_size "
           . "FROM (SELECT tag_id, COUNT(*) as number"
           . "      FROM WebPageTag"
           . "      WHERE webpagequery_id=$webpagequery_id"
           . "      GROUP BY tag_id) AS TagCount"
           . "      LEFT JOIN ("
           . "      SELECT tag_id, SUM(rating) as total_rating"
           . "      FROM WebPageTagRating"
           . "      WHERE webpagequery_id=$webpagequery_id"
           . "      GROUP BY tag_id) as TagRating "
           . "      ON TagCount.tag_id = TagRating.tag_id "
           . "      JOIN Tag ON TagCount.tag_id = Tag.id "
           . "      JOIN (SELECT MIN(number + IFNULL(total_rating, 0)) AS min_font, (MAX(number + IFNULL(total_rating, 0)) - MIN(number + IFNULL(total_rating, 0))) AS max_font "
           . "            FROM (SELECT tag_id, COUNT(*) as number "
           . "                  FROM WebPageTag "
           . "                  WHERE webpagequery_id=$webpagequery_id "
           . "                  GROUP BY tag_id) AS TagCount "
           . "                  LEFT JOIN ( "
           . "                  SELECT tag_id, SUM(rating) as total_rating "
           . "                  FROM WebPageTagRating "
           . "                  WHERE webpagequery_id=$webpagequery_id "
           . "                  GROUP BY tag_id) as TagRating "
           . "                  ON TagCount.tag_id = TagRating.tag_id) AS tab_max "
           . "ORDER BY text ASC";
    $result = mysql_query($query);  
    $return_string .= "<alltag count='" . mysql_num_rows($result) . "'>\n";
    while ($result_row = mysql_fetch_assoc($result)) {
      if ($result_row['total_rating'] == NULL)
        $result_row['total_rating'] = 0;
      $return_string .= "<tag>\n"
                      . "<text>" . $result_row['tag'] . "</text>\n"
                      . "<count>" . $result_row['number'] . "</count>\n"
                      . "<rating>" . $result_row['total_rating'] . "</rating>\n"
                      . "<fontsize>" . $result_row['font_size'] . "</fontsize>\n"
                      . "</tag>\n";
    }

    $return_string .= "</alltag>\n";

    if (isset($_SESSION['user_id'])) {
      $user_id = $_SESSION['user_id'];
      $query = "SELECT text as tag, number, total_rating "
             . "FROM (SELECT tag_id, COUNT(*) as number"
             . "      FROM WebPageTag"
             . "      WHERE webpagequery_id=$webpagequery_id"
             . "      AND user_id=$user_id"
             . "      GROUP BY tag_id) AS TagCount"
             . "      LEFT JOIN ("
             . "      SELECT tag_id, SUM(rating) as total_rating"
             . "      FROM WebPageTagRating"
             . "      WHERE webpagequery_id=$webpagequery_id"
             . "      GROUP BY tag_id) as TagRating "
             . "      ON TagCount.tag_id = TagRating.tag_id "
             . "      JOIN Tag ON TagCount.tag_id = Tag.id";
      $result = mysql_query($query);
      $return_string .= "<usertag count='" . mysql_num_rows($result) . "'>\n";
      while ($result_row = mysql_fetch_assoc($result)) {
        if ($result_row['total_rating'] == NULL)
          $result_row['total_rating'] = 0;
        $return_string .= "<tag>\n"
                        . "<text>" . $result_row['tag'] . "</text>\n"
                        . "<count>" . $result_row['number'] . "</count>\n"
                        . "<rating>" . $result_row['total_rating'] . "</rating>\n"
                        . "</tag>\n";
      }
      $return_string .= "</usertag>\n";
    }

    return $return_string;
  }
  
  function getRecommendedPageTags($url, $url_param) {
    $return_string = "";
   
    $query = "SELECT id FROM WebPage WHERE url = '" . $url . "'";
    $result = mysql_query($query);
    $webpage_id = -1;
    if (mysql_num_rows($result) == 0) {
      $webpage_id = registerPage($url);
    } else {
      $result_row = mysql_fetch_assoc($result);
      $webpage_id = $result_row['id'];
    }

    $webpagequery_id = getPageParameterId($webpage_id, $url_param);

    
    $captured_tags = array();
    $captured_tags[] = -1;
    
    $return_string .= "<alltag>\n";

    
    if (isset($_SESSION['user_id'])) {
      $user_id = $_SESSION['user_id'];
      // tags of user
      $query = "SELECT tag_id FROM WebPageTag WHERE webpagequery_id = $webpagequery_id AND user_id = $user_id";
           
      $result = mysql_query($query);  
      while ($result_row = mysql_fetch_assoc($result)) {
        $captured_tags = addArrayElement($captured_tags, $result_row['tag_id']);
      }
    }
    
    
    // Already existing tags of current page
    $query = "SELECT tab_1.tag_id AS tag_id, tab_1.text AS text, 'existing' AS comment "
           . "FROM (SELECT * FROM WebPageTag wpt, Tag t WHERE wpt.tag_id = t.id AND webpagequery_id = $webpagequery_id) as tab_1 LEFT JOIN "
           . "     (SELECT * FROM WebPageTag WHERE webpagequery_id = $webpagequery_id AND user_id = 3) as tab_2 ON (tab_1.tag_id = tab_2.tag_id) "
           . "WHERE tab_2.user_id IS NULL "
           . "GROUP BY tab_1.tag_id, tab_1.text";
           
    $result = mysql_query($query);  
    while ($result_row = mysql_fetch_assoc($result)) {
      $captured_tags = addArrayElement($captured_tags, $result_row['tag_id']);
      $return_string .= "<tag>\n"
                      . "<text>" . $result_row['text'] . "</text>\n"
                      . "<comment>" . $result_row['comment'] . "</comment>\n"
                      . "</tag>\n";
    }

    
    // Already existing tags of current page
    $query = "SELECT t.id AS tag_id, t.text, 'recommended' AS comment "
           . "FROM (SELECT tags.tag_id, AVG(links.link_rating * tags.cnt) AS popularity "
           . "      FROM (SELECT neighbor, SUM(rating) AS link_rating "
           . "            FROM (SELECT IF(tab_1.from_webpagequery_id = $webpagequery_id, tab_1.to_webpagequery_id, tab_1.from_webpagequery_id) AS neighbor, rating "
           . "                  FROM ((SELECT user_id, from_webpagequery_id, to_webpagequery_id, 1 rating FROM Link) UNION ALL (SELECT rater_user_id, from_webpagequery_id, to_webpagequery_id, rating FROM LinkRating)) tab_1 "
           . "                  WHERE ((tab_1.from_webpagequery_id = $webpagequery_id) OR (tab_1.to_webpagequery_id = $webpagequery_id))) tab_outer "
           . "                  GROUP BY neighbor) links, "
           . "           (SELECT tab.webpagequery_id, tab.tag_id, COUNT(*) AS cnt "
           . "            FROM ((SELECT user_id, webpagequery_id, tag_id, 1 AS rating FROM WebPageTag) UNION (SELECT rater_user_id as user_id, webpagequery_id, tag_id, rating FROM WebPageTagRating)) tab "
           . "            GROUP BY tab.webpagequery_id, tab.tag_id) tags "
           . "      WHERE links.neighbor = tags.webpagequery_id "
           . "      GROUP BY tags.tag_id) tab_outer, Tag t "
           . "WHERE tab_outer.tag_id = t.id "
           . "AND   tab_outer.tag_id NOT IN " . getSqlInString($captured_tags) . " "
           . "AND   tab_outer.popularity > 0 "
           . "ORDER BY tab_outer.popularity DESC "
           . "LIMIT 0, 10";

    $result = mysql_query($query);  
    while ($result_row = mysql_fetch_assoc($result)) {
      $captured_tags = addArrayElement($captured_tags, $result_row['tag_id']);
      $return_string .= "<tag>\n"
                      . "<text>" . $result_row['text'] . "</text>\n"
                      . "<comment>" . $result_row['comment'] . "</comment>\n"
                      . "</tag>\n";
    }
    
    // globally popular tags
    $query = "SELECT t.id AS tag_id, t.text AS text, 'popular' AS comment "
           . "FROM (SELECT tab_1.tag_id, (tab_1.cnt + IFNULL(tab_2.rating, 0)) AS popularity "
           . "      FROM (SELECT tag_id, COUNT(*) AS cnt FROM WebPageTag GROUP BY tag_id) tab_1 "
           . "           LEFT JOIN "
           . "           (SELECT tag_id, SUM(rating) AS rating FROM WebPageTagRating GROUP BY tag_id) tab_2 "
           . "           ON (tab_1.tag_id = tab_2.tag_id)) tab_outer, Tag t "
           . "WHERE tab_outer.tag_id = t.id "
           . "AND   tab_outer.tag_id NOT IN " . getSqlInString($captured_tags) . " "
           . "AND   tab_outer.popularity > 0 "
           . "ORDER BY tab_outer.popularity DESC "
           . "LIMIT 0, 10";

    $result = mysql_query($query);  
    while ($result_row = mysql_fetch_assoc($result)) {
      $captured_tags = addArrayElement($captured_tags, $result_row['tag_id']);
      $return_string .= "<tag>\n"
                      . "<text>" . $result_row['text'] . "</text>\n"
                      . "<comment>" . $result_row['comment'] . "</comment>\n"
                      . "</tag>\n";
    }
    
    
    $return_string .= "</alltag>\n";

 

    return $return_string;
  }
  
?>
